#读取excel的部分数据，转化成excel，注意最后一行自己，号改为;号
import time
import uuid
import pandas as pd
import snowflake.client

# snowflake.client.get_guid()
# print(uid)

#INSERT INTO toptea.i18n_cd_config (ID, NAME, LAN, CONTENT, MODULE, SORT, STATUS, CREATE_TIME, UPDATE_TIME, CREATE_USER, TYPE, mdcid, mdctime) VALUES
# ('119541a1-4279-4a63-aa92-2ab8274dd97c', 'conf_i18n_01itsm_194_respRsnDesc', 'zh_TW', '家寬投訴報結原因', 'common', null, 1, '2021-05-13 16:15:40', '2021-05-13 16:36:59', '003_wxh', 4, 243407905961050112, null);
# ('07bf8407-a111-4364-ab44-fe6ffe2a6c78', 'conf_i18n_01itsm_194_respRsnDesc', 'en_US', 'Home wide complaint', 'common', null, 1, '2021-05-13 16:15:40', '2021-05-13 16:36:59', '003_wxh', 4, 243407905961050113, null);
# ('a0a19b4e-4adb-4b83-9519-d4b117f7baf8', 'conf_i18n_01itsm_194_respRsnDesc', 'zh_CN', '家宽投诉报结原因', 'common', null, 1, '2021-05-13 16:15:40', '2021-05-13 16:36:59', '003_wxh', 4, 243407905961050114, null);

def work(filepath):
    # 指定格式，不然有些字符串比如 '001' 读取后是1
    df=pd.read_excel(filepath, dtype=str)

    # for index, rows in df.iterrows():
    #     # print(index)
    #     l1=rows
    #     for rowI in range(len(rows)):
    #         print(rows[rowI])
    client = snowflake.client
    rstring = "INSERT INTO toptea.i18n_cd_config (ID, NAME, LAN, CONTENT, MODULE, SORT, STATUS, CREATE_TIME, UPDATE_TIME, CREATE_USER, TYPE, mdcid, mdctime) VALUES \n"


    # linenum = df.shape[0]
    # sfList = []
    # for i in range(linenum):
    #还是得睡眠，没啥用
    #     time.sleep(0.05)
    #     sfList.append(snowflake.client.get_guid())
    # print(len(sfList))

    for index, rows in df.iterrows():

        id = rows[0]
        # print(id)

        conftype = rows[1]
        # print(conftype)
        name = rows[2]
        nameus = rows[3]
        nametw=rows[4]

        resultname = 'conf_i18n_'+str(id)+conftype
        # print(resultname)

        lzh_CN = "('"+str(uuid.uuid4())+"', '"+ resultname +"', '" + "zh_CN" + "', '"+name +"', '"+   "common', null, 1, '2021-12-06 16:15:40', '2021-12-06 16:36:59', 'hb_hjx', 4, "+str(client.get_guid())+", null),\n"
        len_US = "('"+str(uuid.uuid4())+"', '"+ resultname +"', '" + "en_US" + "', '"+name +"', '"+   "common', null, 1, '2021-12-06 16:15:40', '2021-12-06 16:36:59', 'hb_hjx',  4, "+str(client.get_guid())+", null),\n"
        lzh_TW = "('"+str(uuid.uuid4())+"', '"+ resultname +"', '" + "zh_TW" + "', '"+name +"', '"+   "common', null, 1, '2021-12-06 16:15:40', '2021-12-06 16:36:59', 'hb_hjx',  4, "+str(client.get_guid())+", null),\n"

        #雪花算法太快报http报错
        #其实这样写不好，一个请求所需要的个数的雪花id，放到数组里，每次遍历的时候自增获取，也可以唯一，然后自己测了没用
        time.sleep(0.01)

        rstring += lzh_CN
        rstring += len_US
        rstring += lzh_TW

    # print(rstring)

    file_write_obj=open('../数据结果集/特点sql.txt', 'w', encoding="utf-8")
    file_write_obj.write(rstring)

    print("文件写入成功")




if __name__ == '__main__':
    file ='../数据测试集/特定sql.xls'
    work(file)